package com.ydtg.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ydtg.bean.Materiel;
import com.ydtg.dao.BaseDao;
import com.ydtg.dao.MaterielDao;

public class MaterielDaoImpl extends BaseDao implements MaterielDao{
	public List<Materiel> getall(Materiel news) {
		// TODO 自动生成的方法存根
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Materiel> list = new ArrayList<Materiel>();
		
		try {
			conn = getconnection();
			String sql = "select id,name,number,science,sourc,pprice,price,personincharge from materiel where 1=1";
			stmt = conn.prepareStatement(sql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Materiel pet  = new Materiel();
				pet.setId(rs.getInt(1));
				pet.setName(rs.getString(2));
				pet.setNumber(rs.getInt(3));
				pet.setScience(rs.getString(4));
				pet.setSourc(rs.getString(5));
				pet.setPprice(rs.getInt(6));
				pet.setPrice(rs.getInt(7));
				pet.setPersonincharge(rs.getString(8));
				list.add(pet);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			this.closeAll(conn, stmt, rs);
		}
		
		return list;
	}

	@Override
	public int add(Materiel pet) {
		
		String psql = "insert into materiel(name,number,science,sourc,pprice,price,personincharge) "
				+ "values(?,?,?,?,?,?,?)";
		Object[] params = {pet.getName(),pet.getNumber(),pet.getScience(),pet.getSourc(),pet.getPprice(),pet.getPrice(),pet.getPersonincharge()};
		
		int count=this.executeUpdate(psql, params);
		return count;
	}

	@Override
	public int updateByID(Materiel pet) {
		
		String psql = "	UPDATE materiel set name=?,number=?,science=?,sourc=?,pprice=?,price=?,personincharge=? where id=?";
		Object[] params = {pet.getName(),pet.getNumber(),pet.getScience(),pet.getSourc(),pet.getPprice(),pet.getPrice(),pet.getPersonincharge(),pet.getId()};
		int count =  this.executeUpdate(psql, params);
		return count;
	}

	@Override
	public int delByID(int id) {
		String psql = "	delete from materiel where id=?";
		Object[] params = {id};
		int count = this.executeUpdate(psql, params);
		return count;
	}

	@Override
	public List<Materiel> getTypefaceList(Materiel pet) {
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Materiel> list = new ArrayList<Materiel>();
		try {
			conn = getconnection();
			String psql = "select id,name,number,science,sourc,pprice,price,personincharge from materiel where 1=1";
			if (pet!=null&&pet.getId()!=0) {
				psql = psql+" and id="+pet.getId();
			}
			if (pet!=null&&pet.getName()!=null&&!"".equals(pet.getName())) {
				psql = psql+" and name="+pet.getName();
			}
			if (pet!=null&&pet.getNumber()!=0) {
				psql = psql+" and number="+pet.getNumber();
			}
			if (pet!=null&&pet.getScience()!=null&&!"".equals(pet.getScience())) {
				psql = psql+" and science="+pet.getScience();
			}
			if (pet!=null&&pet.getSourc()!=null&&!"".equals(pet.getSourc())) {
				psql = psql+" and sourc="+pet.getSourc();
			}
			if (pet!=null&&pet.getPprice()!=0) {
				psql = psql+" and pprice="+pet.getPprice();
			}
			if (pet!=null&&pet.getPrice()!=0) {
				psql = psql+" and price="+pet.getPrice();
			}
			if (pet!=null&&pet.getPersonincharge()!=null&&!"".equals(pet.getPersonincharge())) {
				psql = psql+" and personincharge="+pet.getPersonincharge();
			}
			stmt = conn.prepareStatement(psql);
			rs = stmt.executeQuery();
			while (rs.next()) {
				Materiel pet1  = new Materiel();
				pet1.setId(rs.getInt(1));
				pet1.setName(rs.getString(2));
				pet1.setNumber(rs.getInt(3));
				pet1.setScience(rs.getString(4));
				pet1.setSourc(rs.getString(5));
				pet1.setPprice(rs.getInt(6));
				pet1.setPrice(rs.getInt(7));
				pet1.setPersonincharge(rs.getString(8));
				list.add(pet1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally {
			closeAll(conn, stmt, null);
		}
		return list;
	}

	@Override
	public int shuliang() {
		// TODO 自动生成的方法存根
				Connection conn = null;
				PreparedStatement stmt = null;
				ResultSet rs = null;
				List<Materiel> list = new ArrayList<Materiel>();
				int c=0;
				try {
					conn = getconnection();
					String sql = "select count(id) from materiel where 1=1";
					stmt = conn.prepareStatement(sql);
					rs = stmt.executeQuery();
					while (rs.next()) {
						
						c= rs.getInt(1);
						
					}
				} catch (Exception e) {
					// TODO: handle exception
					e.printStackTrace();
				}finally {
					this.closeAll(conn, stmt, rs);
				}
				
				return c;
	}
}
